<?php

class forum extends Model
{

	function getForum($forumid) {
		$gforum = $this->db->getAll('SELECT
					f.title,
					f.forum_id,
					f.description,
					f.post_no,
					p.date,
					u.lastname,
					u.firstname,
					u.middlename
				FROM forum f 
					LEFT JOIN (post p 
					INNER JOIN user u
					ON p.author_id = u.stud_no)
					ON f.lastpost_id = p.post_id
				WHERE
					f.forum_id = ?
				ORDER BY p.date ASC
					',
			array((int)$forumid)	
		);
		if( count( $gforum) > 0 ) return $gforum[0];
		return false;
	}

	function getForums($orgname) {
		$gforum = $this->db->getAll('SELECT
					f.title,
					f.forum_id,
					f.description,
					f.post_no,
					p.date,
					u.lastname,
					u.firstname,
					u.middlename
				FROM forum f 
					INNER JOIN organization o
					ON f.org_id = o.org_id
					LEFT JOIN (post p 
					INNER JOIN user u
					ON p.author_id = u.stud_no)
					ON f.lastpost_id = p.post_id
				WHERE
					o.name = ?
				ORDER BY p.date ASC
					',
			array($orgname)	
		);
		return $gforum;
	}

	function insertForum( $org_id, $title, $desc, $post_no, $lastpost_id )	{
		$this->db->execute("INSERT INTO forum( org_id,
			title, 
			description, 
			post_no,
			lastpost_id )
			VALUES ( ?, ?, ?, ?, ?	)", 
			array( $org_id, $title, $desc, $post_no, $lastpost_id )
		);
		return $iforum;
	}

	function updateForum( $title, $desc, $forum_id ) 	{
		$uforum = $this->db->execute("UPDATE forum
			SET 
				title = ?,
				description = ?
			WHERE forum_id = ?",
			array( $title, $desc, (int) $forum_id )
		);
		return $uforum;
	}
	
	function deleteForum ( $forum_id )	{
		$dforum = $this->db->execute("DELETE FROM forum
			WHERE forum_id = ? ",
			array( (int) $forum_id )
		);
	}

	function lookupThreadForum( $thread ) {
		$threads = $this->db->getAll("SELECT forum_id FROM thread WHERE thread_id = ?",
			array( (int) $thread) 
		);
		if( count( $threads ) > 0 ) return $threads[0]['forum_id'];
		else return false;
	}
	
	function getForumName($forum) {
		}
	
	function getThread( $thread ) {
		$sql = "
			SELECT 
				thread.reply_no,
				thread.title thtitle,	
				thread.thread_id,
				thread.author_id,
				thread.date,
				thread.view_no,
				post.date,
				user.lastname,
				user.firstname,
				user.middlename
			FROM thread 
				inner JOIN (post
				INNER JOIN user
				ON post.author_id = user.stud_no)
				ON thread.lastpost_id = post.post_id
			WHERE
				thread.thread_id = ?
				ORDER BY post.date ASC
			";
		$threads = $this->db->getAll( $sql, array( (int) $thread ) );
		if( count( $threads ) > 0 ) return $threads[0];
		else return false;
	}
	
	function getThreads( $forum_id ) {
		$sql = "
			SELECT 
				forum.title,
				forum.forum_id,
				forum.description,
				forum.post_no,
				thread.reply_no,
				thread.title thtitle,
				thread.thread_id,
				thread.author_id,
				thread.date,
				thread.view_no,
				post.date,
				user.lastname,
				user.firstname,
				user.middlename
			FROM forum 
				INNER JOIN thread
				ON thread.forum_id = forum.forum_id
				LEFT JOIN (post
				INNER JOIN user
				ON post.author_id = user.stud_no)
				ON thread.lastpost_id = post.post_id
			WHERE
				forum.forum_id = ?
				ORDER BY post.date ASC
			";
		return $this->db->getAll( $sql, array( (int) $forum_id ) );
	}
		
	function insertThread( $forum_id, $title, $author_id, $date, $reply_no, $view_no, $lastpost_id, $message )	{
		$this->db->startTrans();
		try { 
			$this->db->execute("INSERT INTO thread( forum_id,
				title,
				author_id,
				date, 
				reply_no,
				view_no,
				lastpost_id )
				VALUES ( ?, ?, ?, ?, ?, ?, ? )", 
				array( (int) $forum_id, $title, $author_id, $date, $reply_no, $view_no, (int) $lastpost_id )
			);
			$threadId = $this->db->lastInsertId();
			$this->db->execute("
				INSERT INTO post
				(thread_id, author_id, date, message)
				VALUES
				( ?, ?, ?, ? )",
				array(
					(int) $threadId, $author_id, $date, $message
				)				
			);
			$postId = $this->db->lastInsertId();
			$this->db->execute("
				UPDATE thread
				SET 
					reply_no = ?,
					lastpost_id = ?
				WHERE thread_id = ?",
				array(
					1,
					$postId,
					$threadId
				)
			);
			$this->db->execute("
				UPDATE forum
				SET lastpost_id = ?,
					post_no = post_no + 1
				WHERE forum_id = ?",
				array( (int) $postId, $forum_id )
			);
			$this->db->commit();
		} catch( Exception $e ) {
			$this->db->rollBack();
			var_dump( $e->getMessage() );			
		}
		return $ithread;
	}

	function updateThread( $forum_id, $title, $author_id, $date, $reply_no, $view_no, $lastpost_id, $thread_id ) 	{
		$uthread = $this->db->execute("UPDATE thread
			SET forum_id = ?,
				title = ?,
				author_id = ?,
				date = ?,
				reply_no = ?,
				view_no = ?,
				lastpost_id = ?
			WHERE thread_id = ?",
			array( (int) $forum_id, $title, $author_id, $date, $reply_no, $view_no, $lastpost_id, (int) $thread_id )
		);
		return $uthread;
	}

	function deleteThread ( $thread_id )	{
		$dthread = $this->db->execute("DELETE FROM thread
			WHERE thread_id = ? ",
			array( $thread_id )
		);
	}

	function getPost($id)	{
		$gpost = $this->db->getAll("
				SELECT 
					thread.thread_id,
					thread.reply_no,
					thread.title thtitle,
					thread.thread_id,
					thread.author_id,
					thread.date,
					post.post_id,
					thread.view_no,
					post.author_id, 
					post.date pdate, 
					post.message,
					user.lastname,
					user.firstname,
					user.middlename				
			FROM thread
				INNER JOIN post 
				ON thread.thread_id = post.thread_id
				INNER JOIN user
				ON post.author_id = user.stud_no
			WHERE post.post_id = ?
			ORDER BY pdate ASC
			",
			array($id)	
		);
		return $gpost;
	}
	
	function getPosts($id)	{
		$gpost = $this->db->getAll("
				SELECT 
					thread.thread_id,
					thread.reply_no,
					thread.title thtitle,
					thread.thread_id,
					thread.author_id,
					thread.date,
					post.post_id,
					thread.view_no,
					post.author_id, 
					post.date pdate, 
					post.message,
					user.lastname,
					user.firstname,
					user.middlename				
			FROM thread
				INNER JOIN post 
				ON thread.thread_id = post.thread_id
				INNER JOIN user
				ON post.author_id = user.stud_no
			WHERE thread.thread_id = ?
			ORDER BY pdate ASC
			",
			array($id)	
		);
		return $gpost;
	}
	
	function insertPost( $thread_id, $author_id, $date, $message )	{
		$this->db->startTrans();
		try {
			$this->db->execute("INSERT INTO post( 
				thread_id, 
				author_id,
				date,
				message
				) VALUES ( ?, ?, ?, ? )", 
				array( (int) $thread_id, $author_id, $date, $message )
			);
			$postId = $this->db->lastInsertId();
			$res = $this->db->execute("
				UPDATE thread
				SET 
					reply_no = reply_no + 1,
					lastpost_id = ?
				WHERE thread_id = ?",
				array( (int) $postId, (int) $thread_id )
			);
			$fid = $this->lookupThreadForum( $thread_id );
			$this->db->execute("
				UPDATE forum
				SET 
					post_no = post_no + 1,
					lastpost_id = ?
				WHERE forum_id = ?",
				array( (int) $postId, (int) $fid )
			);
			$this->db->commit();
			return true;
		} catch ( Exception $e ) {
			$this->db->rollBack();
			var_dump( $e->getMessage() );
			return false;
		}
	}

	function updatePost( $message, $post_id ) 	{
		$upost = $this->db->execute("
			UPDATE post
			SET message = ?
			WHERE post_id = ?",
			array($message, (int) $post_id )
		);
		return $upost;
	}

	function deletePost ( $post_id )	{
		$this->db->startTrans();
		try {
			// $f = $this->getAll("
				// SELECT f.forum_id,
					// t.thread_id
					// p.post_id
				// FROM post p, thread t, forum f
				// WHERE p.thread_id = t.thread_id
				// AND t.forum_id = f.forum_id
				// AND p.post_id = ?
				// ",
				// array( $post_id )
			// );
			// $f = $f[0];
			// $fid = $f['forum_id'];
			// $tid = $f['thread_id'];
			// $this->db->execute("UPDATE forum INTO post( 
				// thread_id, 
				// author_id,
				// date,
				// message
				// ) VALUES ( ?, ?, ?, ? )", 
				// array( (int) $thread_id, $author_id, $date, $message )
			// );
			// $postId = $this->db->lastInsertId();
			// $res = $this->db->execute("
				// UPDATE thread
				// SET 
					// reply_no = reply_no - 1,
					// lastpost_id = ?
				// WHERE thread_id = ?",
				// array( (int) $postId, (int) $thread_id )
			// );
			// $fid = $this->lookupThreadForum( $thread_id );
			// $this->db->execute("
				// UPDATE forum
				// SET 
					// post_no = post_no + 1,
					// lastpost_id = ?
				// WHERE forum_id = ?",
				// array( (int) $postId, (int) $fid )
			// );
			$dpost = $this->db->execute("DELETE FROM post
				WHERE post_id = ? ",
				array( (int) $post_id )
			);
			$this->db->commit();
			return true;
		} catch ( Exception $e ) {
			$this->db->rollBack();
			var_dump( $e->getMessage() );
			return false;
		}
		
	}
	
	function updateThreadViews( $threadId ) {
		$this->db->execute("
			UPDATE thread
			SET view_no = view_no + 1
			WHERE thread_id = ?",
			array( (int) $threadId )
		);
	}
	
}